"""
Case Type   : 多块预读
Case Name   : 开启多块预读，扫描不同类型的表
Create At   : 2024/11/29
Owner       : @li-xin12345
Description :
    1、创建表预置数据
    2、设置shared_buffers
    3、开启预读，查询表
    4、建表，查询
    5、清理环境
Expect      :
    1、成功
    2、成功
    3、成功
    4、成功
    5、成功
History     :
"""
import os
import unittest

from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class SessionTest(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.common = Common()
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_user_node = Node('PrimaryDbUser')
        self.tb_name = 't_multi_bulk_read_0010'
        self.log_file_default = self.common.show_param('log_filename')
        self.log_name = 'log_multi_bulk_read_0010'
        self.shared_buffer_default = self.common.show_param('shared_buffers')

    def test_multi_bulk_read(self):
        text = '----step1: 创建表预置数据；expect: 成功----'
        self.log.info(text)
        self.log.info('--ustore表--')
        sql = f"drop table if exists {self.tb_name}_1;" \
              f"create table {self.tb_name}_1(c_int int, c_text text) " \
              f"with(storage_type=ustore);" \
              f"insert into {self.tb_name}_1 " \
              f"values(generate_series(1,100), " \
              f"'test'|| generate_series(1,100));"
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res, f'执行失败：{text}')
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res,
                      f'执行失败：{text}')
        self.assertIn('INSERT 0 100', res, f'执行失败：{text}')

        self.log.info('--分区表--')
        sql = f"drop table if exists {self.tb_name}_2 cascade;" \
              f"create table {self.tb_name}_2(prod_id number(6), " \
              f"cust_id number, time_id date, channel_id char(1), " \
              f"promo_id number(6), quantity_sold number(3), " \
              f"amount_sold number(10,2))partition by range " \
              f"(time_id) interval('1 day')( " \
              f"partition p1 values less than ('2024-02-01 00:00:00'), " \
              f"partition p2 values less than ('2024-03-01 00:00:00'));" \
              f"insert into {self.tb_name}_2 " \
              f"values(generate_series(1, 200), 12, " \
              f"'2024-01-10 00:00:00', 'a', 1, 1, 1);" \
              f"insert into {self.tb_name}_2 " \
              f"values(generate_series(1, 200), 12, " \
              f"'2024-02-01 00:00:00', 'a', 1, 1, 1);" \
              f"insert into {self.tb_name}_2 " \
              f"values(generate_series(1, 200), 12, " \
              f"'2024-02-20 00:00:00', 'a', 1, 1, 1);" \
              f"insert into {self.tb_name}_2 " \
              f"values(generate_series(1, 200), 12, " \
              f"'2024-03-01 00:00:00', 'a', 1, 1, 1);" \
              f"insert into {self.tb_name}_2 " \
              f"values(generate_series(1, 200), 12, " \
              f"'2024-06-01 00:00:00', 'a', 1, 1, 1);" \
              f"SELECT t1.relname, partstrategy, boundaries FROM " \
              f"pg_partition t1, pg_class t2 " \
              f"WHERE t1.parentid = t2.oid AND " \
              f"t2.relname = '{self.tb_name}' AND t1.parttype = 'p';"
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res, f'执行失败：{text}')
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res,
                      f'执行失败：{text}')
        self.assertEqual(res.count('INSERT 0 200'), 5, f'执行失败：{text}')

        self.log.info('--二级分区表--')
        sql = f'''drop table if exists {self.tb_name}_3 cascade;
        create table {self.tb_name}_3(month_code varchar2(30) not null, 
        dept_code varchar2(30) not null, user_no varchar2(30) not null, 
        sales_amt int)partition by list (month_code) 
        subpartition by hash (dept_code)(
        partition p_201901 values ('201901')(
        subpartition p_201901_a, subpartition p_201901_b), 
        partition p_201902 values ('201902')(
        subpartition p_201902_a, subpartition p_201902_b));
        insert into {self.tb_name}_3 values('201901', 
        generate_series(1, 100), '1', generate_series(1, 99));
        insert into {self.tb_name}_3 values('201902', 
        generate_series(1, 100), '1', generate_series(1, 99));
        '''
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res, f'执行失败：{text}')
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res,
                      f'执行失败：{text}')
        self.assertEqual(res.count('INSERT 0 9900'), 2, f'执行失败：{text}')

        self.log.info('--行存压缩表--')
        res = self.pri_sh.execute_gsguc(
            'set', self.constant.GSGUC_SUCCESS_MSG,
            'support_extended_features=on')
        self.log.info(res)
        self.assertTrue(res, f'执行失败：{text}')
        restart_res = self.pri_sh.restart_db_cluster()
        self.log.info(restart_res)
        self.assertTrue(restart_res)
        sql = f'''drop table if exists {self.tb_name}_4 cascade;
        create table {self.tb_name}_4(cid int, name varchar2) 
        with (compresstype=2);
        insert into {self.tb_name}_4 values(generate_series(1,500),'name');
        '''
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res, f'执行失败：{text}')
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res,
                      f'执行失败：{text}')
        self.assertIn('INSERT 0 500', res, f'执行失败：{text}')

        text = '----step2: 设置shared_buffers；expect: 成功----'
        self.log.info(text)
        res = self.pri_sh.execute_gsguc(
            'set', self.constant.GSGUC_SUCCESS_MSG, 'shared_buffers=512kB')
        self.log.info(res)
        self.assertTrue(res, f'执行失败：{text}')
        res = self.pri_sh.execute_gsguc(
            'set', self.constant.GSGUC_SUCCESS_MSG,
            f"log_filename='{self.log_name}'")
        self.log.info(res)
        self.assertTrue(res, f'执行失败：{text}')
        restart_res = self.pri_sh.restart_db_cluster()
        self.log.info(restart_res)
        self.assertTrue(restart_res)

        text = '----step3: 开启预读，查询表；expect: 成功----'
        self.log.info(text)
        sql = f'''set heap_bulk_read_size to 16;
        select count(*) from {self.tb_name}_1;
        select count(*) from {self.tb_name}_2 partition(p2);
        select count(*) from {self.tb_name}_2;
        select count(*) from {self.tb_name}_3;
        select count(*) from {self.tb_name}_4;
        '''
        self.log.info(sql)
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.SET_SUCCESS_MSG, res, f'执行失败：{text}')
        self.assertIn('100', res, f'执行失败：{text}')
        self.assertIn('400', res, f'执行失败：{text}')
        self.assertIn('1000', res, f'执行失败：{text}')
        self.assertIn('19800', res, f'执行失败：{text}')
        self.assertIn('500', res, f'执行失败：{text}')

        text = '----step4: 建表，查询；expect: 成功----'
        self.log.info(text)
        self.log.info('--unlogged表--')
        sql = f"drop table if exists {self.tb_name}_5;" \
              f"create unlogged table {self.tb_name}_5 as " \
              f"select * from {self.tb_name}_2;"
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res, f'执行失败：{text}')
        self.assertIn('INSERT 0 1000', res, f'执行失败：{text}')

        self.log.info('--全局临时表--')
        sql = f"drop table if exists {self.tb_name}_6;" \
              f"create global temp table {self.tb_name}_6 as " \
              f"select * from {self.tb_name}_3;"
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res, f'执行失败：{text}')
        self.assertIn('INSERT 0 19800', res, f'执行失败：{text}')

        self.log.info('--本地临时表--')
        sql = f'''drop table if exists {self.tb_name}_7;
        create local temp table {self.tb_name}_7 as 
        select * from {self.tb_name}_1;
        set heap_bulk_read_size to 8;
        select count(*) from {self.tb_name}_5;
        select count(*) from {self.tb_name}_6;
        select count(*) from {self.tb_name}_7;
        '''
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res, f'执行失败：{text}')
        self.assertIn('INSERT 0 100', res, f'执行失败：{text}')
        self.assertIn(self.constant.SET_SUCCESS_MSG, res, f'执行失败：{text}')
        self.assertIn('1000', res, f'执行失败：{text}')
        self.assertEqual(res.count('1 row'), 3, f'执行失败：{text}')

    def tearDown(self):
        text = '----step5: 清理环境;expect:成功----'
        self.log.info(text)
        shared_buffer_res = self.pri_sh.execute_gsguc(
            'set', self.constant.GSGUC_SUCCESS_MSG,
            f'shared_buffers={self.shared_buffer_default}')
        self.log.info(shared_buffer_res)

        log_res = self.pri_sh.execute_gsguc(
            'set', self.constant.GSGUC_SUCCESS_MSG,
            f"log_filename='{self.log_file_default}'")
        self.log.info(log_res)

        compress_res = self.pri_sh.execute_gsguc(
            'set', self.constant.GSGUC_SUCCESS_MSG,
            f"support_extended_features=off")
        self.log.info(compress_res)
        restart_res = self.pri_sh.restart_db_cluster()
        self.log.info(restart_res)

        for i in range(8):
            sql = f'drop table if exists {self.tb_name}_{i} cascade;'
            res = self.pri_sh.execut_db_sql(sql)
            self.log.info(res)
            self.assertIn(self.constant.DROP_TABLE_SUCCESS, res,
                          f'执行失败：{text}')
        self.assertTrue(log_res, f'执行失败：{text}')
        self.assertTrue(shared_buffer_res, f'执行失败：{text}')
        self.assertTrue(compress_res, f'执行失败：{text}')
        self.assertTrue(restart_res)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')